﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data; //一般都有同时引入

namespace thinger.ADONETTeach
{
    /// <summary>
    /// 主要练习基础的数据库连接、insert、update、delete、select
    /// </summary>
    public class CRUDBase
    {
        //封装连接字符串
        private string connString = "server=DESKTOP-0GPLPOL;DataBase=MiSDB;Uid=sa;Pwd=07135214Huwei";
        //连接数据库
        public void ConnectDB()
        {
            //【1】创建Connection对象
            /*SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connString;*/

            SqlConnection conn = new SqlConnection(connString); //创建使用有参数构造方法
            //打开连接
            conn.Open(); 
            if(conn.State == ConnectionState.Open)
            {
                Console.WriteLine("连接已打开");
            }
            //【3】关闭连接
            conn.Close();
            if(conn.State == ConnectionState.Closed)
            {
                Console.WriteLine("连接已经关闭");
            }
        }
        public void Insert()
        {
            //【1】创建连接对象
            SqlConnection conn = new SqlConnection(connString); //创建使用有参数构造方法
            //【2】定义SQL语句
            string sql = "insert into Employee(EployName, Gender, NowAddress, IdNo,WeiXinNumber,PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)";
            sql += "values('Kiter16', '男', '江西', '123456789012345690', 'thinger11', '13370722380', '暂无', '2020-01-13', 13, 13)";
            //【3】创建Command对象
            /*SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = conn;*/
            SqlCommand cmd = new SqlCommand(sql, conn);
            //【4】打开连接
            conn.Open();
            //【5】执行操作
            int result = cmd.ExecuteNonQuery(); /*此方法，可以执行insert、update、delete类型的sql语句，不能执行select*/
            Console.WriteLine($"受影响的行数：{result}");
            //【6】关闭连接
            conn.Close();
        }
        public void Update()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "update Employee set EployName = '王老师', Gender='女' where EployeeId=1005";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            int result = cmd.ExecuteNonQuery();
            Console.WriteLine($"受影响的行数：{result}");
            conn.Close();
        }

        public void Delete()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "delete from Employee where EployeeId=1005";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            int result = cmd.ExecuteNonQuery();
            Console.WriteLine($"受影响的行数：{result}");
            conn.Close();
        }
        //insert和update.delete类型的sql语句执行的过程是完全一样的

        //单一结果的查询
        public void GetSingleResult1()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select count(*) as 员工总数 from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object result = cmd.ExecuteScalar();
            Console.WriteLine(result);
            conn.Close();
        }
        public void GetSingleResult2()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EployName  from Employee where EployeeId=1001";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object result = cmd.ExecuteScalar();
            Console.WriteLine(result);
            conn.Close();
        }
        public void GetSingleResult3()
        {
            SqlConnection conn = new SqlConnection(connString); //创建使用有参数构造方法
            string sql = "insert into Employee(EployName, Gender, NowAddress, IdNo,WeiXinNumber,PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)";
            sql += "values('熊老师', '男', '海南', '123456789012345239', 'thinger21', '13370722311', '暂无', '2020-01-13', 13, 13)";
            sql += ";select @@identity";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object result = cmd.ExecuteScalar();
            Console.WriteLine($"最大的Id  值：{result.ToString()}");
            conn.Close();
        }
        /// <summary>
        /// 返回一个结果集的查询(只读)
        /// </summary>
        public void GetReaderList1()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EployName,Gender,NowAddress,IdNo,WeiXinNumber,PhoneNumber from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            //提交查询返回一个只读数据集
            SqlDataReader reader = cmd.ExecuteReader();
            //使用循环读取
            while(reader.Read())
            {
                Console.WriteLine($"{reader["EployName"]}\t{reader["Gender"]}\t{reader["NowAddress"]}\t{reader["IdNo"]}\t{reader["WeiXinNumber"]}\t{reader[5]}");
            }
            reader.Close(); //关闭读取器(必须在读取完毕)
            conn.Close(); //一定要在读取器关闭后，再关闭，否则出问题
        }
        /// <summary>
        /// 返回多个结果集的查询(只读)
        /// </summary>
        public void GetReaderList2()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EployName,Gender,NowAddress,IdNo,WeiXinNumber,PhoneNumber from Employee";
            sql += ";select DepartmentId,DepartmentName from Department";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            //使用循环读取
            while (reader.Read())
            {
                Console.WriteLine($"{reader["EployName"]}\t{reader["Gender"]}\t{reader["NowAddress"]}\t{reader["IdNo"]}\t{reader["WeiXinNumber"]}\t{reader[5]}");
            }
            Console.WriteLine("--------------------------------------------");
            //跳转到下一个结果集
            if(reader.NextResult())
            {
                while(reader.Read())
                {
                    Console.WriteLine($"{reader["DepartmentId"]}\t{reader["DepartmentName"]}");
                }
            }
            reader.Close(); //关闭读取器(必须在读取完毕)
            conn.Close(); //一定要在读取器关闭后，再关闭，否则出问题
        }
        /// <summary>
        /// 返回一个数据集
        /// </summary>
        public void GetDataSet1()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EployName,Gender,NowAddress,IdNo,WeiXinNumber,PhoneNumber from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            //创建一个数据适配器(sqlDataAdapter)
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            //创建数据集对象DataSet(内存数据库) DataTable
            DataSet ds = new DataSet();

            //填充数据
            da.Fill(ds);
            conn.Close();
            //遍历DataTable
            foreach(DataRow row in ds.Tables[0].Rows)
            {
                Console.WriteLine($"{row["EployName"]}\t{row["Gender"]}\t{row["NowAddress"]}\t{row["IdNo"]}");
            }
        }
        /// <summary>
        /// 返回一个数据集包含多张数据表
        /// </summary>
        public void GetDataSet2()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EployName,Gender,NowAddress,IdNo,WeiXinNumber,PhoneNumber from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            //创建一个数据适配器(sqlDataAdapter)
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //创建数据集对象DataSet(内存数据库) DataTable
            DataSet ds = new DataSet();

            //填充数据(第一个查询结果)
            da.Fill(ds, "Employee");
            //封装第二个查询
            cmd.CommandText = "select DepartmentId,DepartmentName from Department";
            da.Fill(ds, "Department");
            cmd.CommandText = "select PostId,PostName from Post";
            da.Fill(ds, "Post");
            conn.Close();

            //遍历DataTable
            foreach(DataTable dt in ds.Tables)
            {
                Console.WriteLine($"{dt.TableName}\t{dt.Rows.Count}");
            }
            Console.WriteLine("--------------------------------");
            //遍历第一个表的DataTable的数据行
            foreach (DataRow row in ds.Tables["Employee"].Rows)
            {
                Console.WriteLine($"{row["EployName"]}\t{row["Gender"]}\t{row["NowAddress"]}\t{row["IdNo"]}");
            }
            Console.WriteLine("--------------------------------");
            foreach (DataRow row in ds.Tables["Department"].Rows)
            {
                Console.WriteLine($"{row["DepartmentId"]}\t{row["DepartmentName"]}");
            }
            Console.WriteLine("--------------------------------");
            foreach (DataRow row in ds.Tables["Post"].Rows)
            {
                Console.WriteLine($"{row["PostId"]}\t{row["PostName"]}");
            }
        }
    }
}
